using Glpt.Common;
using Glpt.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace Glpt.Task
{
    /// <summary>
    /// 公共函数类
    /// </summary>
    public class CommFuns
    {
        /// <summary>
        /// 动态执行存储过程把数据覆盖到模板表
        /// </summary>
        /// <param name="dt_mod">模板表</param>
        /// <param name="exp">表达式</param>
        /// <param name="function_id">功能号</param>
        /// <param name="tradedate">业务日期</param>
        /// <returns>结果集</returns>
        public static DataTable GetDynamicProc(DataTable dt_mod, string exp, string function_id, string tradedate)
        {
            int db_no = 0;
            string sql = Funcs.ReplaceWord(exp, tradedate).Replace("@FUNCTION_ID", function_id);
            int idx = sql.IndexOf(":");
            if (idx > 0)
            {
                string DB_NO = sql.Substring(0, idx);
                int.TryParse(DB_NO, out db_no);
                sql = sql.Substring(idx + 1);
            }
            DbParamCollection sql_params = null;
            bool is_pro = true;
            BaseInterFace sif = new BaseInterFace(db_no);
            if (sql.ToUpper().StartsWith("SELECT") || sql.ToUpper().StartsWith("EXEC"))
                is_pro = false;
            else
            {
                sql_params = new DbParamCollection();
                sql_params.Add(new DbParam("TRADEDATE", tradedate, DbParamType.VarChar, false));
                sql_params.Add(new DbParam("FUNCTION_ID", function_id, DbParamType.Int, false));
            }
            DataTable dt = sif.ExecSelect(null, sql, sql_params, is_pro, -1);
            dt_mod.Clear();
            foreach (DataRow dr in dt.Rows)
            {
                DataRow drow = dt_mod.NewRow();
                foreach (DataColumn dc in dt_mod.Columns)
                {
                    if (dt.Columns.Contains(dc.ColumnName))
                        drow[dc.ColumnName] = dr[dc.ColumnName];
                }
                dt_mod.Rows.Add(drow);
            }
            return dt;
        }
        /// <summary>
        /// 动态执行函数
        /// </summary>
        /// <param name="bif">数据库连接</param>
        /// <param name="exp">表达式</param>
        /// <param name="colname">字段名</param>
        /// <returns>结果集</returns>
        public static DataTable GetDynamicFunc(BaseInterFace bif, string exp, string colname)
        {
            if (exp.Contains("<<<"))
            {
                string[] sql_list = exp.Split(new string[] { "<<<" }, StringSplitOptions.RemoveEmptyEntries);
                if (sql_list.Length < 2) throw new Exception("配置错误，缺少SQL语句");
                DataTable dt0 = ExecDynamicSQL(bif, sql_list[0], colname);
                if (dt0.Columns.Count < 2) throw new Exception("配置错误，缺少列");
                DataTable dt1 = ExecDynamicSQL(bif, sql_list[1], colname);
                if (dt1.Columns.Count < 2) throw new Exception("配置错误，缺少列");
                DataTable dt = dt0.Clone();
                foreach (DataRow dr0 in dt0.Rows)
                {
                    string key0 = dr0[0].ToString();
                    int value0 = int.Parse(dr0[1].ToString());
                    int value1 = 0;
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        string key1 = dr1[0].ToString();
                        if (key0.Equals(key1))
                        {
                            value1 = int.Parse(dr1[1].ToString());
                            break;
                        }
                    }
                    if (value0 > value1)
                    {
                        dr0[1] = value1;
                        dt.Rows.Add(dr0.ItemArray);
                    }
                }
                return dt;
            }
            else
                return ExecDynamicSQL(bif, exp, colname);
        }
        /// <summary>
        /// 动态执行函数
        /// </summary>
        /// <param name="bif">数据库连接</param>
        /// <param name="exp">表达式</param>
        /// <param name="colname">字段名</param>
        /// <returns>结果集</returns>
        public static DataTable ExecDynamicSQL(BaseInterFace bif, string exp, string colname)
        {
            BaseInterFace sif = bif;
            string sql = exp;
            int idx = sql.IndexOf(":");
            if (idx > 0)
            {
                int DB_NO = 0;
                if (int.TryParse(sql.Substring(0, idx), out DB_NO))
                {
                    sif = new BaseInterFace(GetDbItem(DB_NO, DateTime.Now.ToString("yyyyMMdd")));
                    sql = sql.Substring(idx + 1);
                }
            }
            if (!sql.ToUpper().StartsWith("SELECT") && !sql.ToUpper().StartsWith("EXEC ") && !sql.ToUpper().StartsWith("CALL "))
            {
                sql = "SELECT " + sql;
                if (colname.Length > 0) sql += " AS " + colname;
                if (!sif.DB_TYPE.Equals(DataBaseType.SQLServer)) sql += " FROM DUAL";
            }
            object[] PMS = SplitSQL(sif.DB_TYPE, sql);//分拆SQL语句
            return sif.ExecSelect(null, (string)PMS[0], (DbParamCollection)PMS[1], (bool)PMS[2], -1);
        }
        /// <summary>
        /// 执行日期函数
        /// </summary>
        /// <param name="bif">数据库连接</param>
        /// <param name="exp">表达式</param>
        /// <param name="tradedate">日期</param>
        /// <param name="dict">缓存</param>
        /// <returns></returns>
        public static string GetDateExpResult(BaseInterFace bif, string exp, string tradedate, Dictionary<string, string> dict)
        {
            string BUSI_DATE = tradedate;
            if (exp.Length > 0)
            {
                string key = tradedate + ":" + exp;
                if (dict.ContainsKey(key))
                    BUSI_DATE = dict[key];
                else
                {
                    BUSI_DATE = GetDynamicFunc(bif, Funcs.ReplaceWord(exp, BUSI_DATE), "BUSI_DATE").Rows[0][0].ToString();
                    dict.Add(key, BUSI_DATE);
                }
            }
            return BUSI_DATE;
        }

        /// <summary>
        /// 获取数据库项目信息
        /// </summary>
        /// <param name="u_db_no">数据库编号</param>
        /// <param name="tradedate">业务日期</param>
        /// <returns>数据库项目</returns>
        public static DbItem GetDbItem(int u_db_no, string tradedate)
        {
            DbItem dbi = null;
            if (u_db_no >= 0)
            {
                dbi = BaseInterFace.DbList.GetByNo(u_db_no);
                if (dbi == null)
                    throw new Exception("没有找到数据库编号" + u_db_no.ToString());
            }
            else
            {
                string s_db_no = (0 - u_db_no).ToString();
                string sql = "SELECT * FROM TB_EXCH_DATABASE WHERE DB_NO=" + s_db_no;
                DataTable dt_source = new BaseInterFace().ExecSelect(sql);
                if (dt_source.Rows.Count == 0)
                    throw new Exception("没有找到数据库编号" + s_db_no);
                else
                {
                    DataRow dr = dt_source.Rows[0];
                    dr["DB_IP"] = Funcs.ReplaceWord(dr["DB_IP"].ToString(), tradedate).Trim();
                    dr["DB_NAME"] = Funcs.ReplaceWord(dr["DB_NAME"].ToString(), tradedate).Trim();
                    dbi = new DbItem(dr);
                }
            }
            return dbi;
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="arguments">参数</param>
        public static string Execute(string cmd, string arguments)
        {
            string ret_msg = InvokeDynamic.InvokeEXE(cmd, arguments);
            string[] log_msg = ret_msg.Split(new string[] { DataFuns.NewLineString }, StringSplitOptions.RemoveEmptyEntries);
            if (cmd.EndsWith("exp.exe") || cmd.EndsWith("imp.exe"))
            {
                Dictionary<string, string> param = DataFuns.SplitStringValue(arguments, ' ');
                if (param.ContainsKey("log"))
                {
                    log_msg = File.ReadAllText(param["log"], DataFuns.DefaultEncoding).Split(new string[] { DataFuns.NewLineString }, StringSplitOptions.RemoveEmptyEntries);
                    if (log_msg.Length > 0)
                        ret_msg = log_msg[log_msg.Length - 1];
                    foreach (string msg in log_msg)
                    {
                        if (msg.StartsWith("EXP-") || msg.StartsWith("ORA-"))
                        {
                            ret_msg += "错误" + msg;
                            break;
                        }
                    }
                }
            }
            else if (cmd.Equals("osql"))
            {
                if (log_msg.Length >= 2 && log_msg[0].StartsWith("消息"))
                    throw new Exception(log_msg[1]);
            }
            else
            {
                bool has_error = false;
                foreach (string msg in log_msg)
                {
                    if (has_error)
                        throw new Exception(msg);
                    else if (msg.StartsWith("Error =") && msg.IndexOf("警告: ") < 0)
                    {
                        int idx = msg.IndexOf("SQL Server]");
                        if (idx > 0)
                            throw new Exception(msg.Substring(idx + 11));
                        else
                            throw new Exception(msg);
                    }
                    else if (msg.StartsWith("CTLIB Message:") || msg.StartsWith("Server Message:"))
                    {
                        has_error = true;
                    }
                    else if (msg.StartsWith("已复制 "))
                        ret_msg = msg;
                }
            }
            return ret_msg;
        }
        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="userid">用户名和密码</param>
        /// <param name="control_file">控制文件</param>
        /// <param name="log_file">日志文件</param>
        public static void Dmfldr(string arguments, string control_file, string log_file, string bad_file)
        {
            if (File.Exists(bad_file))
                File.Delete(bad_file);
            arguments = arguments + " control='" + control_file + "' log='" + log_file + "' direct=true";
            InvokeDynamic.InvokeEXE("dmfldr", arguments);
            if (File.Exists(log_file))
            {
                string[] log_msg = File.ReadAllLines(log_file, DataFuns.DefaultEncoding);
                StringBuilder errmsg = new StringBuilder();
                bool is_sucess = false;
                foreach (string msg in log_msg)
                {
                    if (msg.StartsWith("由于数据格式错误,") && !msg.StartsWith("由于数据格式错误,0行"))
                        errmsg.AppendLine(msg);
                    else if (msg.StartsWith("由于数据错误,") && !msg.StartsWith("由于数据错误,0行"))
                        errmsg.AppendLine(msg);
                    else if (msg.StartsWith("失败原因:")|| msg.EndsWith("load failed."))
                        errmsg.AppendLine(msg);
                    else if (msg.EndsWith("行加载成功。") || msg.EndsWith("load success."))
                        is_sucess = true;
                }
                if (!is_sucess && log_msg.Length > 0)
                    errmsg.AppendLine(log_msg[log_msg.Length - 1]);
                if (errmsg.Length > 0)
                    throw new Exception(errmsg.ToString());
            }
        }

        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="userid">用户名和密码</param>
        /// <param name="control_file">控制文件</param>
        /// <param name="log_file">日志文件</param>
        public static void Sqlldr(string arguments, string control_file, string log_file, string bad_file)
        {
            if (File.Exists(bad_file))
                File.Delete(bad_file);
            arguments = arguments + " control=" + control_file + " log=" + log_file + " direct=true";
            InvokeDynamic.InvokeEXE("sqlldr", arguments);
            if (File.Exists(log_file))
            {
                string[] log_msg = File.ReadAllLines(log_file, DataFuns.DefaultEncoding);
                StringBuilder errmsg = new StringBuilder();
                foreach (string msg in log_msg)
                {
                    if (msg.StartsWith("ORA-"))
                        errmsg.AppendLine(msg);
                    else if (msg.Contains(": 被拒绝"))
                        errmsg.AppendLine(msg);
                }
                if (errmsg.Length == 0)
                {
                    foreach (string msg in log_msg)
                    {
                        if (msg.StartsWith("SQL*Loader-"))
                            errmsg.AppendLine(msg);
                    }
                }
                if (errmsg.Length > 0)
                    throw new Exception(errmsg.ToString());
            }
        }
        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="userid">用户名和密码</param>
        /// <param name="filename">控制文件</param>
        public static void Dbisql(string userid, string filename)
        {
            string arguments = "dbisql -c " + userid + " -onerror exit -nogui " + filename;
            string ret_msg = InvokeDynamic.InvokeCMD(arguments, new string[] { "echo %ERRORLEVEL%" });
            string[] log_msg = ret_msg.Split(new string[] { DataFuns.NewLineString }, StringSplitOptions.RemoveEmptyEntries);
            if (log_msg.Length > 0)
            {
                int imsg = int.Parse(log_msg[log_msg.Length - 1]);
                if (imsg > 0)
                {
                    string msg = "";
                    switch (imsg)
                    {
                        case 0: msg = "成功"; break;
                        case 1: msg = "常规失败"; break;
                        case 2: msg = "文件格式无效"; break;
                        case 3: msg = "文件未找到，无法打开"; break;
                        case 4: msg = "内存不足"; break;
                        case 5: msg = "被用户终止"; break;
                        case 6: msg = "通信失败"; break;
                        case 7: msg = "缺少一个必需的数据库名"; break;
                        case 8: msg = "客户端/服务器协议不匹配"; break;
                        case 9: msg = "无法连接到数据库服务器"; break;
                        case 10: msg = "数据库服务器未运行"; break;
                        case 11: msg = "未找到数据库服务器"; break;
                        case 12: msg = "加密密钥遗失或错误"; break;
                        case 13: msg = "必须升级服务器才能运行数据库"; break;
                        case 14: msg = "文件不是数据库"; break;
                        case 15: msg = "日志文件遗失或其它错误"; break;
                        case 16: msg = "文件正在使用"; break;
                        case 17: msg = "发生致命错误"; break;
                        case 18: msg = "缺少服务器许可文件"; break;
                        case 19: msg = "中止后台同步以允许进行优先级较高的操作"; break;
                        case 20: msg = "访问被拒绝，数据库无法启动"; break;
                        case 255: msg = "命令行上的参数无效"; break;
                    }
                    throw new Exception(msg);
                }
            }
        }
        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="filename">文件</param>
        public static int Db2CMD(string filename)
        {
            string arguments = "db2cmd -i -w " + filename;
            string ret_msg = InvokeDynamic.InvokeCMD(arguments);
            string[] log_msg = ret_msg.Split(new string[] { DataFuns.NewLineString }, StringSplitOptions.RemoveEmptyEntries);
            string[] err_key = new string[] { "跳过行数         = ", "拒绝行数         = ", "删除行数         = ", "SQL3119W  ", "SQL3123W  " };
            string suc_key = "落实行数         = ";
            int rows = 0;
            foreach (string msg in log_msg)
            {
                foreach (string key in err_key)
                {
                    if (msg.StartsWith(key) && !msg.Substring(key.Length).Trim().Equals("0"))
                    {
                        throw new Exception(msg);
                    }
                }
                if (msg.StartsWith(suc_key))
                {
                    rows = int.Parse(msg.Substring(suc_key.Length));
                }
            }
            if (rows == 0)
                throw new Exception("没有找到落实行数");
            return rows;
        }
        public static char StringSplitChar = '^';
        public static void AddParams(Dictionary<string, string> exec_params, DataTable dt_params)
        {
            foreach (DataColumn dc in dt_params.Columns)
            {
                string val = "";
                for (int i = 0; i < dt_params.Rows.Count; i++)
                {
                    if (i > 0) val += StringSplitChar.ToString();
                    val += dt_params.Rows[i][dc.ColumnName].ToString().Replace(StringSplitChar.ToString(), " ");
                }
                AddParams(exec_params, dc.ColumnName, val);
            }
        }
        public static void AddParams(Dictionary<string, string> exec_params, string name, string val)
        {
            string key = "@" + name;
            if (exec_params.ContainsKey(key))
                exec_params[key] = val;
            else
                exec_params.Add(key, val);
        }
        public static Encoding GetEncoding(string encode_name)
        {
            if (encode_name.Length == 0 || encode_name.ToLower().Equals("default"))
                return DataFuns.DefaultEncoding;
            else if (encode_name.Equals("utf-8e"))
                return new UTF8Encoding(false);
            else
                return Encoding.GetEncoding(encode_name);
        }
        public static int[] GetRandomArray(int length)
        {
            int[] items = new int[length];
            for (int i = 0; i < length; i++)
            {
                items[i] = i;
            }
            if (length > 1)
            {
                Random rnd = new Random();
                byte[] keys = new byte[length];
                rnd.NextBytes(keys);
                Array.Sort(keys, items);
            }
            return items;
        }

        public static bool IsDbf(string ext)
        {
            bool isdbf = false;
            DateTime dt = new DateTime(2000, 1, 1, 0, 0, 0);
            DateTime dt_end = new DateTime(2000, 12, 31, 0, 0, 0);
            while (dt.CompareTo(dt_end) <= 0)
            {
                string val = "." + int.Parse(dt.ToString("MM")).ToString("x") + dt.ToString("dd");
                if (val.Equals(ext))
                {
                    isdbf = true;
                    break;
                }
                dt = dt.AddDays(1);
            }
            return isdbf;
        }
        /// <summary>
        /// 分拆参数字符串
        /// </summary>
        /// <param name="dbt">数据库类别</param>
        /// <param name="paramString">字符串</param>
        /// <returns></returns>
        public static object[] SplitSQL(DataBaseType dbt, string paramString)
        {
            string sql = "";
            DbParamCollection sql_params = null;
            bool is_pro = false;
            if (paramString.ToUpper().StartsWith("CALL ") && dbt.Equals(DataBaseType.Oracle))
            {
                List<string> values = new List<string>();
                int ibegin = 0;
                bool isbegin = false;
                for (int i = 0; i < paramString.Length; i++)
                {
                    bool is_last = (i == paramString.Length - 1);
                    string chr = paramString.Substring(i, 1);
                    if (chr.Equals("'")) isbegin = !isbegin;
                    string val = "";
                    if ((chr.Equals(" ") || chr.Equals(",")) && !isbegin)
                    {
                        val = paramString.Substring(ibegin, i - ibegin);
                        ibegin = i + 1;
                    }
                    else if (is_last)
                    {
                        val = paramString.Substring(ibegin);
                    }
                    if (val.Length > 0)
                    {
                        if (val.StartsWith("'"))
                            values.Add(val.Substring(1, val.Length - 2));
                        else
                            values.Add(val);
                    }
                }
                if (values.Count > 1) sql = values[1];
                if (values.Count > 2)
                {
                    sql_params = new DbParamCollection();
                    for (int j = 2; j < values.Count; j++)
                    {
                        DbParam dp = new DbParam("P" + (j - 1).ToString(), DbParamType.VarChar);
                        if (values[j].Equals("@out"))
                            dp.Direction = ParameterDirection.Output;
                        else
                            dp.Value = values[j];
                        sql_params.Add(dp);
                    }
                }
                is_pro = true;
            }
            else
                sql = paramString;
            return new object[] { sql, sql_params, is_pro };
        }
    }
    /// <summary>
    /// 数据文件类别
    /// </summary>
    public enum DataFileType
    {
        /// <summary>
        /// 数据库
        /// </summary>
        DATABASE = 0,
        /// <summary>
        /// BCP文本文件
        /// </summary>
        BCP = 1,
        /// <summary>
        /// DBF文件
        /// </summary>
        DBF = 2,
        /// <summary>
        /// Excel文件
        /// </summary>
        XLS = 3,
        /// <summary>
        /// CSV文件
        /// </summary>
        CSV = 4,
        /// <summary>
        /// XML文件
        /// </summary>
        XML = 5,
        /// <summary>
        /// JSON文件
        /// </summary>
        JSON = 6,
        /// <summary>
        /// HTML文件
        /// </summary>
        HTML = 7,
        /// <summary>
        /// PDF文件
        /// </summary>
        PDF = 8,
        /// <summary>
        /// DOC文件
        /// </summary>
        DOC = 9,
        /// <summary>
        /// DLL文件
        /// </summary>
        DLL = 12
    }
}
